package com.dldata.devtools30.common;

import com.alibaba.fastjson.JSON;
import com.dldata.devtools30.utils.DBTypeUtil;
import com.dldata.devtools30.utils.DateUtil;
import com.dldata.devtools30.utils.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.*;
import java.util.*;

@Slf4j
public class PageUtil {

    public static final String ORDER_BY = "orderBy";
    public static final String PAGE_INDEX = "pageIndex";
    public static final String PAGE_SIZE = "pageSize";

    public static PageUtil newPageUtil(Map<String,Object> paraMap, String sql, String countselect){
        CommonPageExec commonPage=new CommonPageExec();
        commonPage.setCountselect(countselect);
        commonPage.setSql(sql);
        LinkedHashMap orderMap=new LinkedHashMap();
        String orderBy=(String)
        paraMap.getOrDefault(ORDER_BY,"");
        if(!StringUtil.isEmptyOrLength0(orderBy)){
            String[] orderByAry=orderBy.split(",");
            for (int i = 0; i < orderByAry.length; i++) {
                String[] orderByAryOne = orderByAry[i].split("@");
                if(!StringUtil.isEmptyOrLength0(orderByAryOne[0])){
                    orderMap.put(orderByAryOne[0],(orderByAryOne.length==1||StringUtil.isEmptyOrLength0(orderByAryOne[1]))?"desc":orderByAryOne[1]);
                }
            }
        }
        commonPage.setOrderMap(orderMap);
        commonPage.setPageIndex(StringUtil.isEmptyOrLength0(paraMap.get(PAGE_INDEX))?null:Long.valueOf(paraMap.get(PAGE_INDEX).toString()));
        commonPage.setPageSize(StringUtil.isEmptyOrLength0(paraMap.get(PAGE_SIZE))?null:Long.valueOf(paraMap.get(PAGE_SIZE).toString()));
        commonPage.setParaMap(paraMap);
        return new PageUtil(commonPage);
    }

    private CommonPageExec commonPageExec;

    private PageUtil(CommonPageExec commonPageExec){
        this.commonPageExec=commonPageExec;
    }

    public CommonPage execSearch(NamedParameterJdbcTemplate jdbcTemplate){
        commonPageExec.execSearch(jdbcTemplate);
        return commonPageExec;
    }

    public static class CommonPageExec extends CommonPage{

        public void execSearch(NamedParameterJdbcTemplate jdbcTemplate){
            DBTypeUtil.DBTYPE dbtype= DBTypeUtil.getDbtype(((JdbcTemplate)jdbcTemplate.getJdbcOperations()).getDataSource());
            if(!StringUtil.isEmptyOrLength0(countselect)){
                String func_Sql_TotalTmp = sql.toLowerCase().replaceAll("\\s"," ").
                        replaceFirst("select.*?\\s+from\\s+", "select count(1) as total from ");
                int endIndex = func_Sql_TotalTmp.toLowerCase().lastIndexOf(" order by ");
                func_Sql_TotalTmp=endIndex>0?func_Sql_TotalTmp.substring(0, endIndex):func_Sql_TotalTmp;
                List<Map<String, Object>> dataList= jdbcTemplate.queryForList(func_Sql_TotalTmp, paraMap);
                total=dataList.size()==0?0:Long.parseLong(dataList.get(0).get("total").toString());
            }else{
                Boolean isMaxLongClown=Boolean.FALSE;
                String func_Sql_Tmp = sql.toString().toLowerCase().replaceAll("\\s"," ").replaceFirst("select.*?\\s+from\\s+",
                        "select * from ").replace(" where "," where 1=2    ");
                func_Sql_Tmp=func_Sql_Tmp.substring(0,func_Sql_Tmp.indexOf(" where 1=2 ")+12);
                if(DBTypeUtil.DBTYPE.sqlserver.equals(dbtype)){
                    func_Sql_Tmp = sql.toString().toLowerCase().replaceAll("\\s"," ").replaceFirst("select.*?\\s+from\\s+",
                            "select top 0 * from ");
                }
                try{
                    isMaxLongClown=
                            jdbcTemplate.query(func_Sql_Tmp,paraMap,new ResultSetExtractor<Boolean>(){
                                @Override
                                public Boolean extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                                    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                                    for (int j = 1; j  <= resultSetMetaData.getColumnCount(); j ++) {
                                        if(resultSetMetaData.getColumnDisplaySize(j )>3096){
                                            return Boolean.TRUE;
                                        }
                                        ;
                                    }
                                    return Boolean.FALSE;
                                }
                            });
                }catch (Throwable throwable){}


                StringBuilder func_Sql_Total =  new StringBuilder();
                if(isMaxLongClown){
                    String func_Sql_TotalTmp = sql.toString().toLowerCase();
                    if(DBTypeUtil.DBTYPE.sqlserver.equals(dbtype)){
                        int endIndex = func_Sql_TotalTmp.toString().toLowerCase().lastIndexOf(" order by ");
                        func_Sql_TotalTmp=endIndex>0?func_Sql_TotalTmp.substring(0, endIndex):func_Sql_TotalTmp;
                    }
                    func_Sql_Total.append(" select count(1) as total from ( ");
                    func_Sql_Total.append(func_Sql_TotalTmp);
                    func_Sql_Total.append(" ) sqltotalTmp ");
                }else{
                    String func_Sql_TotalTmp = sql.toLowerCase().replaceAll("\\s"," ").
                            replaceFirst("select.*?\\s+from\\s+", "select count(1) as total from ");
                    int endIndex = func_Sql_TotalTmp.toString().toLowerCase().lastIndexOf(" order by ");
                    func_Sql_TotalTmp=endIndex>0?func_Sql_TotalTmp.substring(0, endIndex):func_Sql_TotalTmp;
                    func_Sql_Total.append(func_Sql_TotalTmp);
                }
                List<Map<String, Object>> dataList=new LinkedList<>();
                dataList = jdbcTemplate.queryForList(func_Sql_Total.toString(), paraMap);
                total=dataList.size()==0?0:Long.parseLong(dataList.get(0).get("total").toString());
            }

            execPageDataSelect(jdbcTemplate,dbtype);
        }

        private void execPageDataSelect(NamedParameterJdbcTemplate jdbcTemplate, DBTypeUtil.DBTYPE dbtype) {

            //解析分页数据
            //页码从1开始,所以每一页的起始数应该是:(页码-1)*每页个数-1
            pageIndex=(pageIndex==null||pageIndex<1)?1:pageIndex;
            pageSize=(pageSize==null||pageSize<0)?15:pageSize;
            long start=(pageIndex-1)*pageSize;
            start=start<0?0:start;

            int skipNum=0;
            String searchSql=sql;
            if(DBTypeUtil.DBTYPE.mysql.equals(dbtype)){
                searchSql=sql+ getStringOrderBy() + " limit "+start+" , "+pageSize;
            }else if(DBTypeUtil.DBTYPE.oracle.equals(dbtype)){
                StringBuilder tmpStringBuilder= new StringBuilder();
                tmpStringBuilder.append("select * from ");
                tmpStringBuilder.append(" ( ");
                tmpStringBuilder.append(searchSql.replace("select ", "select rownum as rownum_id,"));
                tmpStringBuilder.append(getStringOrderBy());
                tmpStringBuilder.append(" ) ");
                tmpStringBuilder.append(" where rownum_id between "+start+" and "+(start+pageSize));
                searchSql=searchSql.trim().toLowerCase().indexOf("select")==0?tmpStringBuilder.toString():searchSql;
            }else if(DBTypeUtil.DBTYPE.sqlserver.equals(dbtype)){

                String tmpStr=searchSql.trim().startsWith("select")?"select":"";
                tmpStr=searchSql.trim().startsWith("SELECT")?"SELECT":tmpStr;

                if(tmpStr.length()>0){
                    String toLowerCase = searchSql.replaceFirst(tmpStr, "").trim().toLowerCase();
                    boolean hasDistinct=false;
                    if(toLowerCase.startsWith("distinct")){
                        toLowerCase=toLowerCase.replaceFirst("distinct","");
                        hasDistinct=true;
                    }
                    String searchSql1Tmp="select top 0 "+toLowerCase;
                    List<String> strFieldsList=new LinkedList<>();
                    jdbcTemplate.query(searchSql1Tmp,paraMap, new ResultSetExtractor<Object>() {
                        @Override
                        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                            ResultSetMetaData resultSetMetaData=rs.getMetaData();
                            for (int i=0;i<resultSetMetaData.getColumnCount();i++){
                                resultSetMetaData.getColumnLabel(i+1);
                                resultSetMetaData.getColumnName(i+1).toLowerCase();
                                strFieldsList.add(resultSetMetaData.getColumnName(i+1).toLowerCase());
                            }
                            return null;
                        }
                    });

                    String rownumberOrderbyField ="";
                    if(orderMap.size()>0){
                        rownumberOrderbyField=getStringOrderBy().toString();
                    }else{
                        rownumberOrderbyField=" order by "+(strFieldsList.size()>2?strFieldsList.get(1):strFieldsList.get(0));
                    }
                    StringBuilder tmpStringBuilder= new StringBuilder();
                    tmpStringBuilder.append("select row_number() over ( ");
                    tmpStringBuilder.append(rownumberOrderbyField);
                    tmpStringBuilder.append(" ) rownumber , ");

                    if(hasDistinct){
                        searchSql=searchSql.replaceFirst("distinct","");
                    }
                    searchSql = searchSql.replaceFirst(tmpStr, tmpStringBuilder.toString());

                    //处理最终查询语句
                    StringBuilder tmpStringBuilder1 = new StringBuilder();
                    tmpStringBuilder1.append(" select ");
                    if(hasDistinct){
                        tmpStringBuilder1.append(" distinct ");
                    }
                    tmpStringBuilder1.append(" *  from (");
                    tmpStringBuilder1.append(searchSql);
                    tmpStringBuilder1.append(") tableabc where rownumber between ");
                    tmpStringBuilder1.append(start+1);
                    tmpStringBuilder1.append(" and ");
                    tmpStringBuilder1.append((start+pageSize));
                    searchSql = tmpStringBuilder1.toString();
                }

            }else {
                //TODO:其它数据库
            }

            final int skipNumFinal=skipNum;
            log.info("searchSql: "+searchSql);
            log.info("searchSqlArgs: "+ JSON.toJSONString(paraMap));

            LinkedList linkedList = jdbcTemplate.query(searchSql, paraMap, new ResultSetExtractor<LinkedList<Map<String, Object>>>() {
                @Override
                public LinkedList<Map<String, Object>> extractData(ResultSet sqlRowSet) throws SQLException, DataAccessException {
                    return paraseOneStatementResult(sqlRowSet, skipNumFinal);
                }
            });
            log.debug(" sql end.");
        }

        private StringBuilder getStringOrderBy() {
            StringBuilder orderby=new StringBuilder();
            for(Map.Entry<String,String> entry:orderMap.entrySet()){
                orderby.append(" ");
                orderby.append(entry.getKey());
                orderby.append(" ");
                orderby.append(entry.getValue());
                orderby.append(",");
            }
            if(orderby.length()>0){
                orderby.insert(0," order by ");
                orderby.setLength(orderby.length()-1);
            }
            return orderby;
        }

        private LinkedList<Map<String, Object>> paraseOneStatementResult(ResultSet sqlRowSet, int skipNumFinal) throws SQLException {
            //使用最后一个数据集

            int tmpSkipCount = 0;
            ResultSetMetaData metaData = sqlRowSet.getMetaData();
            while (sqlRowSet.next()) {
                tmpSkipCount++;
                if (tmpSkipCount <= skipNumFinal) {
                    continue;
                }
                Map<String,Object> oneData = new LinkedHashMap<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnLabel(i);
                    switch (metaData.getColumnType(i)){
                        case Types.DATE:{
                            Date anInt = sqlRowSet.getDate(i);
                            String dateString =anInt==null?"": DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, anInt);
                            oneData.put(columnName, dateString);
                            break;
                        }
                        case Types.TIME:{
                            Time time =  sqlRowSet.getTime(i);
                            String dateString =time==null?"": DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(time.getTime()));
                            oneData.put(columnName, dateString);
                            break;
                        }
                        case Types.TIMESTAMP:{
                            Object timestamp1 = sqlRowSet.getObject(i);
                            Timestamp timestamp=new Timestamp(0);
                            if(timestamp1 instanceof oracle.sql.TIMESTAMP ){
                                oracle.sql.TIMESTAMP timestampTmp = (oracle.sql.TIMESTAMP)timestamp1;
                                try {
                                    timestamp=timestampTmp.timestampValue();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }else if(timestamp1 instanceof oracle.sql.DATE ){
                                oracle.sql.DATE timestampTmp = (oracle.sql.DATE)timestamp1;
                                try {
                                    timestamp=timestampTmp.timestampValue();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }else if(timestamp1 instanceof oracle.sql.TIMESTAMPTZ ){
                                oracle.sql.TIMESTAMPTZ timestampTmp = (oracle.sql.TIMESTAMPTZ)timestamp1;
                                try {
                                    timestamp=timestampTmp.timestampValue();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }else{
                                timestamp = sqlRowSet.getTimestamp(i);
                            }
                            String dateString =timestamp==null?"": DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(timestamp.getTime()));
                            oneData.put(columnName, dateString);
                            break;
                        }
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.INTEGER:{;
                            long anInt = sqlRowSet.getLong(i);
//                            oneData.put(columnName,String.valueOf(anInt));
                            BigDecimal bigDecimal=new BigDecimal(anInt);
                            oneData.put(columnName,bigDecimal);
                            break;
                        }
                        case Types.FLOAT:{;
                            float anInt = sqlRowSet.getFloat(i);
//                            oneData.put(columnName,String.valueOf(anInt));
                            BigDecimal bigDecimal=new BigDecimal(anInt);
                            oneData.put(columnName,bigDecimal);
                            break;
                        }
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE:{;
                            double anInt = sqlRowSet.getDouble(i);
//                            oneData.put(columnName,String.valueOf(anInt));
                            BigDecimal bigDecimal=new BigDecimal(anInt);
                            oneData.put(columnName,bigDecimal);
                            break;
                        }
                        case Types.CLOB:{;
                            try {
                                Clob colb= (Clob)sqlRowSet.getObject(i);
                                oneData.put(columnName,colb==null?"":colb.getSubString(1,(int)colb.length()));
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        case Types.BLOB:{;
                            try {
                                Blob blob= (Blob)sqlRowSet.getObject(i);
//                            oneData.put(columnName,new String(blob.getBytes(0, (int) blob.length()),"UTF-8"));
                                //@TODO 后面考虑转二进制可显示串
                                oneData.put(columnName,blob==null?"":blob.toString());
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        default:{
                            String anInt = sqlRowSet.getString(i);
                            anInt=anInt==null?"":anInt;
                            try {
//                            anInt=URLEncoder.encode(anInt,"UTF-8");
                                //支持1.0数据有编码
//                            anInt= URLDecoder.decode(anInt, "UTF-8");
//                            anInt= EscapeUnescape.escape(anInt);
//                            anInt =  StringUtil.escape(anInt);
                            } catch (Exception e) {
                            }
                            oneData.put(columnName, anInt);
                            break;
                        }
                    }
                }

                List list=new LinkedList(oneData.keySet());                ;
                for (Iterator iterator = list.iterator(); iterator.hasNext(); ) {
                    Object next = iterator.next();
                    oneData.put(next.toString().toUpperCase(),oneData.get(next));
                    oneData.put(next.toString().toLowerCase(),oneData.get(next));
                }

                resultlist.add(oneData);
            }
            return resultlist;
        }
    }
}
